package com.rg1906.dao.Impl;

import java.sql.SQLException;
import java.util.List;

import javax.sql.DataSource;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.rg1906.entity.UserList;
import com.rg1906.utils.DataSourceC3P0ThreadLocal;

public class UserListDaoImpl {
	DataSource dataSource = DataSourceC3P0ThreadLocal.getDataSource();
	QueryRunner qr = new QueryRunner(dataSource);
	//查找用户列表
	public List<UserList> queryUserList(String pageNumber, String pageSize, String loginName, int rid, String likeName, String likeRole) throws SQLException {
		int parseInt = Integer.parseInt(pageNumber);
		int parseInt2 = Integer.parseInt(pageSize);
		Object[] par= {parseInt*parseInt2,parseInt2};
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT ");
		sb.append("t.id,t.loginName,t.loginPassword,t1.roleName ");
		sb.append("from ");
		sb.append("t_user t ");
		sb.append("LEFT JOIN ");
		sb.append("t_role t1 ");
		sb.append("on ");
		sb.append("t.rid=t1.id ");
		sb.append("where ");
		if(loginName.startsWith("S")&&rid==4) {
			sb.append(" t.loginName = '"+loginName+"' and t.rid="+rid+" ");
		}else if(loginName.startsWith("T")&&rid==3) {
			sb.append(" t.loginName = '"+loginName+"' and t.rid="+rid+" ");
		}else if(rid==1) {
			sb.append("1=1 ");
		}
		if(likeName!=null&&!"".equals(likeName)) {
			sb.append("and t.loginName like '%"+likeName+"%' ");
		}
		if(likeRole!=null&&!"".equals(likeRole)) {
			sb.append("and t1.id = "+likeRole+" ");
		}
		sb.append("limit ?,?");
		List<UserList> list = qr.query(sb.toString(), new BeanListHandler<>(UserList.class),par);
			return list;
	}
	public Object getCount(String loginName, int rid, String likeName, String likeRole) throws SQLException {
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT ");
		sb.append("count(0) ");
		sb.append("from ");
		sb.append("t_user t ");
		sb.append("LEFT JOIN ");
		sb.append("t_role t1 ");
		sb.append("on ");
		sb.append("t.rid=t1.id ");
		sb.append("where ");
		if(loginName.startsWith("S")&&rid==4) {
			sb.append(" t.loginName = '"+loginName+"' and t.rid="+rid+" ");
		}else if(loginName.startsWith("T")&&rid==3) {
			sb.append(" t.loginName = '"+loginName+"' and t.rid="+rid+" ");
		}else if(rid==1) {
			sb.append("1=1 ");
		}
		if(likeName!=null&&!"".equals(likeName)) {
			sb.append("and t.loginName like '%"+likeName+"%' ");
		}
		if(likeRole!=null&&!"".equals(likeRole)) {
			sb.append("and t1.id = "+likeRole+" ");
		}
		return qr.query(sb.toString(),new ScalarHandler<>());
		
	}
}
